package com.david.utils;


import com.david.model.api.ExcelData;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.awt.Color;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;


@Component
public class ExportExcelUtils {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    private static ExportExcelUtils exportExcelUtils;

    @Autowired
    ObjectMapper objectMapper;

    public @PostConstruct
    void init(){
        exportExcelUtils=this;
    }

    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = "Sheet1";
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
            wb.write(out);
        }
        catch (Exception ex)
        {
            exportExcelUtils.logger.error("导出异常：");
        }
        finally {
            wb.close();
            out.flush();
            out.close();
        }
    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) throws NoSuchMethodException, IllegalAccessException {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        //autoSizeColumns(sheet, data.getTitles().size() + 1);
    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;
        Font titleFont = wb.createFont();
        titleFont.setFontName("仿宋_GB2312");
        titleFont.setBold(true);
        // titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle titleStyle = (XSSFCellStyle)wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//XSSFCellStyle.SOLID_FOREGROUND
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        Row titleRow = sheet.createRow(rowIndex);
        // titleRow.setHeightInPoints(25);
        colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }

    /**
     *  设置导出Excel的数据列
     * @param wb
     * @param sheet
     * @param rows  List<T> 中T为实体 并且属性为私有的  并设置取值和设置函数  函数为驼峰命名  要不然获取不到值
     * @param rowIndex
     * @return
     */
    private static <T> int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<T> rows, int rowIndex) throws NoSuchMethodException, IllegalAccessException {
        int colIndex = 0;
        Font dataFont = wb.createFont();
        dataFont.setFontName("仿宋_GB2312");
        // dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        for (T rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;
            Field[] fields=rowData.getClass().getDeclaredFields();//getDeclaredFields()获得某个类的所有申明的字段，即包括public、private和proteced，但是不包括父类的申明字段。
            for (Field cellData : fields) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cellData.setAccessible(true);
                    Object value=cellData.get(rowData);
                    if(value!=null) {
                        cell.setCellValue(value.toString());
                    }
                    else{
                        cell.setCellValue("");
                    }
                } else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

//    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
//        try
//        {
//            for (int i = 0; i < columnNumber; i++) {
//                int orgWidth = sheet.getColumnWidth(i);
//                sheet.autoSizeColumn(i, true);
//                int newWidth = (int) (sheet.getColumnWidth(i) + 100);
//                if (newWidth > orgWidth) {
//                    sheet.setColumnWidth(i, newWidth);
//                } else {
//                    sheet.setColumnWidth(i, orgWidth);
//                }
//            }
//        }
//       catch (Exception ex)
//       {
//           exportExcelUtils.logger.error("autoSizeColumns方法异常："+ExceptionStackTraceUtils.getStackTrace(ex.fillInStackTrace()));
//       }
//    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }
}
